package course.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import course.db.DbConnection;
import course.po.Course;
import course.po.Teacher;
import course.view.StudentFrame;
import course.dao.ICourseDao;

public class CourseDaoImpl implements ICourseDao {
	/**
	 *
	 * 新增课程
	 *
	 */
	@Override
	public void save(Course course) {
		Connection conn = DbConnection.getConnection();
	    Teacher teacher = course.getTeacher();
		int couId = course.getCouId();
		String couType = course.getCouType();
		String couNo = course.getCouNo();
		String couName = course.getCouName();
		String couDetail = course.getCouDetail();
		int couCredit = course.getCouCredit();
		int  teaId =teacher.getTeaId();
		int maxNumber = course.getMaxNumber();
		int selectNumber = course.getSelectNumber();
		String sql = "INSERT INTO course VALUES(?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, couId);
			ps.setString(2, couType);
			ps.setString(3, couNo);
			ps.setString(4, couName);
			ps.setString(5, couDetail);
			ps.setInt(6, couCredit);
			ps.setInt(7, teaId);
			ps.setInt(8, maxNumber);
			ps.setInt(9, selectNumber);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}
	}

	/**
	 *
	 * 更新课程信息
	 *
	 */
	@Override
	public void update(Course course) {
		Connection conn = DbConnection.getConnection();
		  Teacher teacher = course.getTeacher();
		int couId = course.getCouId();
		String couType = course.getCouType();
		String couNo = course.getCouNo();
		String couName = course.getCouName();
		String couDetail = course.getCouDetail();
		int couCredit = course.getCouCredit();
		int  teaId =teacher.getTeaId();
		String teaName = teacher.getTeaName();
		int maxNumber = course.getMaxNumber();
		int selectNumber = course.getSelectNumber();
		String sql = "UPDATE course,teacher SET course_type=?,course_no=?,course_name=?,course_time_place=?,course_credit=?"
				+ ",course_teacher_id=?, teacher_name=?,course_max_number=?,course_select_number=?  WHERE teacher.teacher_id=course.course_teacher_id AND course_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, couType);
			ps.setString(2, couNo);
			ps.setString(3, couName);
			ps.setString(4, couDetail);
			ps.setInt(5, couCredit);
			ps.setInt(6, teaId);
			ps.setString(7, teaName);
			ps.setInt(8, maxNumber);
			ps.setInt(9, selectNumber);
			ps.setInt(10, couId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 *
	 * 删除课程信息
	 */
	@Override
	public void delete(int id) {
		Connection conn = DbConnection.getConnection();
		String sql = "DELETE FROM course WHERE course_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}
	/**
	 * 根据课程名查询课程的信息 
	 *
	 */
	@Override
	public Course getOne(String name) {
		Connection conn = DbConnection.getConnection();
		List<Course> courseList = new ArrayList<Course>();
		String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit"
				+ ",course_teacher_id,teacher_name,course_max_number,course_select_number FROM course,teacher WHERE teacher.teacher_id=course.course_teacher_id AND course_name=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,name);
			rs = ps.executeQuery();
			while (rs.next()) {
				int couId = rs.getInt(1);
				String couType = rs.getString(2);
				String couNo = rs.getString(3);
				String couName = rs.getString(4);
				String couDetail = rs.getString(5);
				int couCredit = rs.getInt(6);
				int  teaId = rs.getInt(7);
				String teaName = rs.getString(8);
				int maxNumber = rs.getInt(9);
				int selectNumber = rs.getInt(10);
				Teacher teacher = new Teacher(teaId,teaName);
				Course course = new Course(couId, couType, couNo, couName, couDetail, couCredit,  maxNumber, selectNumber,teacher);
				courseList.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return courseList.get(0);
	}
	/**
	 * 根据编号查询课程的信息 course_id为主键
	 *
	 */
	@Override
	public Course get(int id) {
		Connection conn = DbConnection.getConnection();
		List<Course> courseList = new ArrayList<Course>();
		String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit"
				+ ",course_teacher_id,teacher_name,course_max_number,course_select_number FROM course,teacher WHERE teacher.teacher_id=course.course_teacher_id AND course_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				int couId = rs.getInt(1);
				String couType = rs.getString(2);
				String couNo = rs.getString(3);
				String couName = rs.getString(4);
				String couDetail = rs.getString(5);
				int couCredit = rs.getInt(6);
				int  teaId = rs.getInt(7);
				String teaName = rs.getString(8);
				int maxNumber = rs.getInt(9);
				int selectNumber = rs.getInt(10);
				Teacher teacher = new Teacher(teaId,teaName);
				Course course = new Course(couId, couType, couNo, couName, couDetail, couCredit,  maxNumber, selectNumber,teacher);
				courseList.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return courseList.get(0);
	}
	
	//学生选课界面查询得到的课程
<<<<<<< HEAD
	public List<Course> getCourse(String term) {
			Connection conn = DbConnection.getConnection();
			List<Course> courseList = new ArrayList<Course>();
			String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit"
					+ ",course_teacher_id,teacher_name,course_max_number,course_select_number FROM course,teacher WHERE teacher.teacher_id=course.course_teacher_id  AND course_type=?";
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1,term);
=======
	public List<Course> getCourse() {
			Connection conn = DbConnection.getConnection();
			List<Course> courseList = new ArrayList<Course>();
			String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit"
					+ ",course_teacher_id,teacher_name,course_max_number,course_select_number FROM course,teacher WHERE teacher.teacher_id=course.course_teacher_id  AND course_type=?";
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1,StudentFrame.t1.getText());
>>>>>>> refs/remotes/origin/master
				rs = ps.executeQuery();
				while (rs.next()) {
					int couId = rs.getInt(1);
					String couType = rs.getString(2);
					String couNo = rs.getString(3);
					String couName = rs.getString(4);
					String couDetail = rs.getString(5);
					int couCredit = rs.getInt(6);
					int teaId = rs.getInt(7);
					String teaName = rs.getString(8);
					int maxNumber = rs.getInt(9);
					int selectNumber = rs.getInt(10);
					 Teacher teacher  = new Teacher(teaId,teaName);
					Course course = new Course(couId, couType, couNo, couName, couDetail, couCredit, maxNumber, selectNumber,teacher);
					courseList.add(course);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbConnection.close(rs, ps);
			}
	    return courseList;
		}
	/**
	 *
	 * 查询所有课程的信息
	 */
	@Override
	public List<Course> findAll() {
		Connection conn = DbConnection.getConnection();
		List<Course> courseList = new ArrayList<Course>();
		String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit"
				+ ",course_teacher_id,teacher_name,course_max_number,course_select_number FROM course,teacher WHERE teacher.teacher_id=course.course_teacher_id ";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int couId = rs.getInt(1);
				String couType = rs.getString(2);
				String couNo = rs.getString(3);
				String couName = rs.getString(4);
				String couDetail = rs.getString(5);
				int couCredit = rs.getInt(6);
				int  teaId = rs.getInt(7);
				String teaName = rs.getString(8);
				int maxNumber = rs.getInt(9);
				int selectNumber = rs.getInt(10);
				Teacher teacher = new Teacher(teaId,teaName);
				Course course = new Course(couId, couType, couNo, couName, couDetail, couCredit,  maxNumber, selectNumber,teacher);
				courseList.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return courseList;
	}
}

	
